package com.wxg.sqlite;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class SqliteUtils {
	
	private static final Logger LOG = LoggerFactory.getLogger(SqliteUtils.class);
	
	public static final String DB_OPENAPISQLITE = "openapisqlite.db";
	
	public static final String DB_ENGLISHWORD = "dbstore/EnglishWord.db";
	
	private static String database = null;

	static {
		database = DB_ENGLISHWORD;
	}
	
	private SqliteUtils(){}
	
	/**
	 * 
	 * `jdbc:sqlite:openapisqlite.db`
	 * `jdbc:sqlite:dbstore/EnglishWord.db`
	 * 
	 * @return
	 */
	public static Connection getConnection(){
		Connection connection = null;
		if(null == database){
			throw new RuntimeException("database is null. You should invoke `setDatabase`");
		}
		String tpl = "jdbc:sqlite:{1}";
		String dbUrl = tpl.replace("{1}", database);
		try {
			connection = DriverManager.getConnection(dbUrl);
		} catch (SQLException e) {
			LOG.info(e.getMessage());
			e.printStackTrace();
		}
		return connection;
	}
	
	/**
	 * 
	 * @param conn
	 */
	public static void closeConnection(Connection conn){
		if(null != conn){
			try {
				conn.close();
			} catch (SQLException e) {
				LOG.info(e.getMessage());
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 验证表是否存在！
	 * 
	 * true : exist
	 * flase: not exist
	 * 
	 * @param tableName
	 * @return
	 */
	public static boolean verifyTableExist(String tableName){
		boolean flag = false;
		String tpl = "SELECT COUNT(1) FROM sqlite_master WHERE type='table' AND name='{1}'";
		String sql = tpl.replace("{1}", tableName);
		Connection conn = null;
		try {
			conn = getConnection();
			Statement stmt = conn.createStatement();
			stmt.setQueryTimeout(30);
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				if(rs.getInt(1) > 0){
					flag = true;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			LOG.info(e.getMessage());
		} finally{
			closeConnection(conn);
		}
		return flag;
	}

	public static void setDatabase(String database) {
		SqliteUtils.database = database;
	}


	
	
	
	
}